﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ncp.Business
{
    public class ADInfoBll
    {
        /// <summary>
        /// 得到广告信息表
        /// </summary>
        public static DataTable GetADInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select * from bi_ADInfo_v";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        public static DataTable GetADInfoTable(string fields,string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select "+fields+" from bi_ADInfo_v";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        //查询第几条到底几条记录
        public static DataTable GetAdInfoBetween(string fields, string p_Where,string start,string end, string pDataBase)
        {
            string sqlStr = "select " + fields + " from (SELECT *, ROW_NUMBER() OVER (order by id desc) as RowNumber FROM bi_ADInfo_v ";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            sqlStr += ") as aa where aa.RowNumber between " + start + " and " + end + " order by aa.sort";
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到分页广告信息表
        /// </summary>
        public static DataTable GetADInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            if (dicWhere != null)
            {
                foreach (KeyValuePair<string, object> kvp in dicWhere)
                {
                    if (kvp.Key!="more")
                    {
                        pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                        ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                    }
                    else
                    {
                        pcWhere += " and " + kvp.Value;
                    }
                }
            }
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ADInfo_v", pcFieldList, "id", pcOrderBy, pcWhere);
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetADInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, string pcWhere, string p_DataBase)
        {
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ADInfo_v", pcFieldList, "id", pcOrderBy, pcWhere);
            return CommonBll.GetdtList(sqlStr, p_DataBase);
        }

        /// <summary>
        /// 得到广告记录数
        /// </summary>
        public static int GetADInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_ADInfo_v";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }

        /// <summary>
        /// 修改广告信息
        /// </summary>
        public static int UpdateADInfo(Dictionary<string, object> dic, string pDataBase)
        {
            string sqlStr = "UPDATE dbo.bi_ADInfo " +
                            "SET Client_Id = @client_id," +
                            "AD_Path = @ad_path," +
                            "AD_Size = @ad_size," +
                            "AD_StartDate = @ad_startdate," +
                            "AD_EndDate = @ad_enddate," +
                            "AD_Location = @ad_location, " +
                            "AD_Status = @ad_status " +
                            "where AD_Id = @ad_id";
	
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@client_id", dic["client_id"]),
                                           new SqlParameter("@ad_path", dic["ad_path"]),
                                           new SqlParameter("@ad_size", dic["ad_size"]),
                                           new SqlParameter("@ad_startdate", dic["ad_startdate"]),
                                           new SqlParameter("@ad_enddate", dic["ad_enddate"]),
                                           new SqlParameter("@ad_location", dic["ad_location"]),
                                           new SqlParameter("@ad_status", dic["ad_status"]),
                                           new SqlParameter("@ad_id",dic["ad_id"])
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 增加广告信息
        /// </summary>
        public static int InsertADInfo(Dictionary<string, object> dic, string pDataBase)
        {

            
            string values = "";
            string fields = "";
            List<SqlParameter> ilist = new List<SqlParameter>();
            if (dic.Count == 0)
            {
                return 0;
            }
            foreach (KeyValuePair<string, object> kvp in dic)
            {
                fields += "," + kvp.Key;
                values += ",@" + kvp.Key;

                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            string sqlStr = "INSERT INTO dbo.ads (" + fields.Substring(1) + ") values (" + values.Substring(1) + ")";
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 删除广告
        /// </summary>
        public static int DeleteADInfo(string AD_Id, string pDataBase)
        {
            string sqlStr = "delete from ads where id in(" + AD_Id + ")";
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到广告分类
        /// </summary>
        public static DataTable GetAdClass( string pDataBase)
        {
            string sqlStr = "select * from adclass";
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        //删除分类并将该分类下的广告移到默认分类下
        public static int DeleteAdClass(string adClassId, string pDataBase)
        {
            string sqlStr = "delete from adclass where id in(" + adClassId + ") update ads set adid=1 where adid=" + adClassId;
            return CommonBll.ExecuteNonQuery(sqlStr, pDataBase);
        }

        //修改广告分类
        public static int UpdateAdClass(int adClassId, string adClassName, int width, int height, string pDataBase)
        {
            string sqlStr = " update adclass set classname=@classname,defwidth=@defwidth,defheight=@defheight where id=" + adClassId;
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@classname", adClassName),
                                           new SqlParameter("@defwidth", width),
                                           new SqlParameter("@defheight", height)
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        //添加广告分类
        public static int InsertAdClass(string adClassName,int width,int height,string pDataBase)
        {
            string sqlStr = "IF not EXISTS (Select classname From adclass Where classname =@classname) insert adclass(classname,defwidth,defheight) values(@classname,@defwidth,@defheight)";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@classname", adClassName),
                                           new SqlParameter("@defwidth", width),
                                           new SqlParameter("@defheight", height)
                                       };
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        //修改广告
        public static int UpdateAdsInfo(Dictionary<string, object> dicNews, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            string fields = "";
            if (dicNews.Count == 0)
            {
                return 0;
            }
            foreach (KeyValuePair<string, object> kvp in dicNews)
            {
                fields += "," + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            fields = fields.Substring(1);
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;

                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = "update ads set " + fields + " where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

    }
}
